package dbLayer;

import modelLayer.Customer;

import java.sql.*;

public class DBCustomer implements DBCustomerIF {
	
	private Connection con;
	
	public DBCustomer () {
		
		this.con = DBConnection.getInstance().getDBcon();
		
	}
	
	public Customer findCustomerID(int customerID) {
		
		String wClause = "customerID = '" + customerID + "'"; 
		return singleWhere(wClause);
		
	}
	
	public Customer findCustomerName(String customerName) {
		
		String wClause = "name like '%" + customerName + "%'";
		return singleWhere(wClause);
		
	}
	
	private Customer singleWhere(String wClause) {
		
		ResultSet results;
		Customer customer = new Customer();
		String query = buildQuery(wClause);
		try {
			Statement stmt = this.con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				customer = buildObject(results);
				stmt.close();
			}
			else {
				customer = null;
			}
		}
 		catch(Exception e) {
	 		System.out.println("Query exception: "+e);
 		}
		return customer;
		
	}
	
	private Customer buildObject(ResultSet results) {
		
		Customer customer = null;
		try {
			Statement stmt = this.con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet cityResults = stmt.executeQuery(buildQueryCity("zipCode = " + results.getInt("zipCode")));
			String city = "Ukendt";
			if (cityResults.next()) {
				city = cityResults.getString("city");
			}						
			customer = new Customer (results.getInt("customerID"),
										results.getString("name"),
										results.getString("address"),
										city,
										results.getString("country"),
										results.getString("phoneNo"),
										results.getString("email"),
										results.getInt("zipCode"));
		}
		catch(Exception e) {
            System.out.println("Der opstod en fejl i oprettelsen af et customer object.");
		}
		return customer;
		
	}
	
	private String buildQueryCity(String wClause) {
		
		String query = "SELECT city FROM ZipCode_City";
		
		if (wClause.length()>0) {
			query = query + " where " + wClause;
		}
		System.out.println(query);
		return query;
		
	}
	
	private String buildQuery(String wClause) {
		
		String query = "SELECT customerID, name, address, country, phoneNo, email, zipCode FROM customer";
		
		if (wClause.length()>0) {
			query = query + " where " + wClause;
		}
		return query;
		
	}
	
}
